import com.example.demo.service.requestRvo.QueryListRVO;
import com.example.demo.service.requestRvo.annotation.UseField;
import org.junit.jupiter.api.Test;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.example.demo.controller.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Parameter;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Iterator;
import java.util.concurrent.atomic.AtomicReference;
import org.apache.poi.ss.usermodel.*;
import java.sql.*;
import java.io.FileInputStream;
public class JavaType2Interface {

    /**
     * RVO 转为类型和初始对象
     */
    @Test
    public void rvo2Obj() {
//        Class<任意类> RVOClass = 任意类.class;
        Class<QueryListRVO> RVOClass = QueryListRVO.class;
        Field[] fields = ReflectUtil.getFields(RVOClass);

        System.out.println("export interface " + RVOClass.getSimpleName() + " {");
        Arrays.stream(fields).forEach(field -> {
            System.out.println(field.getName() + "?: " + changeType(field));
        });
        System.out.println("}");

        System.out.println("**************************** OBJECT ***************************-");

        AtomicReference<Boolean> flag = new AtomicReference<>(true);

        System.out.println("const " + toLowerCaseFirstOne(RVOClass.getSimpleName()) + ":" + RVOClass.getSimpleName() +" = {");
        Arrays.stream(fields).forEach(field -> {
            if(flag.get()){
                System.out.println(field.getName() + ": undefined,");
                flag.set(false);
            }else {
                System.out.println(field.getName() + ": undefined,");

            }
        });
        System.out.println("}");
    }

    static String changeType(Field field){

        if(field.getType().equals(Integer.class) || field.getType().equals(BigDecimal.class)){
            return "number";
        }else {
            return "string";
        }

    }

    public static String toLowerCaseFirstOne(String s) {
        if(Character.isLowerCase(s.charAt(0)))
            return s;
        else
            return (new StringBuilder()).append(Character.toLowerCase(s.charAt(0))).append(s.substring(1)).toString();

    }


    /**
     * 替换控制层的类 获取 前端 network 下面的ts信息 生成api
     */
//        public static void main(String[] args) {
    @Test
    public void controller2Ts(){

//        Class<controller类> controllerClass = controller类.class;
        Class<helloController> controllerClass = helloController.class;
        Method[] methods = ReflectUtil.getMethods(controllerClass);

        RequestMapping constructorAnnotation = controllerClass.getAnnotation(RequestMapping.class);
        String constructorAnnotationValue;
        String[] constructorAnnotationValues = constructorAnnotation.value();
        if(constructorAnnotationValues == null){
            constructorAnnotationValue = "";
        }else {
            constructorAnnotationValue = constructorAnnotationValues[0];
        }

        System.out.println("export default {");
        Arrays.stream(methods).forEach(method -> {

            PostMapping annotation = method.getAnnotation(PostMapping.class);
            String name = method.getName();

            if(annotation == null){

                return;
            }
            Parameter[] parameters = method.getParameters();
            String[] value = annotation.value();
            if(parameters.length == 0){
                System.out.println(name + "(){");
                System.out.println("     return request.post(\""+ constructorAnnotationValue + value[0] +"\")");
                System.out.println("},");
                return;
            }

            String className = parameters[0].getType().getSimpleName();


            System.out.println(name + "(data:" + className + "){");
            System.out.println("     return request.post(\""+ constructorAnnotationValue + value[0] +"\",data)");
            System.out.println("},");

        });

        System.out.println("}");

    }

    /**
     *  RVO 利用UseField注解 生成表单字段对象
     */
//    public static void main(String[] args) {
    @Test
    public void rvo2Fields(){
//        Class<任意requestRVO> rvoClass = 任意requestRVO.class;

        Class<QueryListRVO> rvoClass = QueryListRVO.class;

        UseField annotation = rvoClass.getAnnotation(UseField.class);
        String listName = annotation.listName();

        Field[] fields = ReflectUtil.getFields(rvoClass);

        System.out.println( "const " + listName +  "FormFieldList = reactive([");

        Arrays.stream(fields).forEach(field -> {

            UseField fieldAnnotation = field.getAnnotation(UseField.class);

            if(fieldAnnotation == null){
                return;
            }
            String name = field.getName();
            if(!StrUtil.isEmpty(fieldAnnotation.name())){
                name = fieldAnnotation.name();
            }

            String type = fieldAnnotation.type().get();
            String label = fieldAnnotation.value();
            System.out.println("    {");
            System.out.println("        name: \"" + name + "\",");
            System.out.println("        label: \"" + label + "\",");
            System.out.println("        type: \"" + type + "\"");
            System.out.println("    },");

        });
        System.out.println("])");
    };

    @Test
    public void uploadExcelData(){

        String jdbcURL = "jdbc:postgresql://localhost:5432/hysz_sponge?currentSchema=public";
        String username = "postgres";
        String password = "root123456";
        String excelFilePath = "E:\\data\\hy_spong\\origin.xlsx";

        try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
            FileInputStream inputStream = new FileInputStream(excelFilePath);
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = firstSheet.iterator();
            connection.setAutoCommit(false);
            String sql = "INSERT INTO hy_parking (id, parent_tenant_id, tenant_id, parking_lot_number, parking_lot_name," +
                    " parking_lot_type, district, street, total_spots, available_spots, " +
                    "parking_lot_form, hourly_rate, charging_standard, price_level, longitude, " +
                    "latitude, address, main_image, creator_user, creation_time, " +
                    "modifier_user, modification_time, remark, is_deleted,is_public, " +
                    "business_hours, property_company, phone, tags, merchant_number, " +
                    "huize_parking_lot_number, discount_rule, discount_details, location) " +
                    "VALUES (?, ?, ?, ?, ?," +
                    "?, ?, ?, ?, ?," +
                    "?, ?, ?, ?, ?," +
                    "?, ?, ?, ?, ?," +
                    "?, ?, ?, ?, ?," +
                    "?, ?, ?, ?, ?," +
                    "?, ?, ?,ST_Transform(ST_SetSRID(ST_MakePoint(?, ?), 4326), 4547))";
            PreparedStatement statement = connection.prepareStatement(sql);


            while (rowIterator.hasNext()) {
                Row nextRow = rowIterator.next();
                if (nextRow.getRowNum() == 0) {
                    // 跳过标题行
                    continue;
                }
                System.out.println(nextRow.getLastCellNum());

                for (int colIndex = 0; colIndex < 33; colIndex++) { // 保证处理 33 个列
                    Cell cell = nextRow.getCell(colIndex, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);

                    if (cell != null) {
                        if (colIndex == 0) { // 假设第一列是 id
                            double idValue = cell.getNumericCellValue();
                            statement.setInt(1, (int) Math.round(idValue));
                        } else {
                            switch (cell.getCellType()) {
                                case STRING:
                                    statement.setString(colIndex + 1, cell.getStringCellValue());
                                    break;
                                case NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        // 对于日期类型的数据
                                        statement.setDate(colIndex + 1, new java.sql.Date(cell.getDateCellValue().getTime()));
                                    } else {
                                        // 对于其他数字类型的数据
                                        statement.setDouble(colIndex + 1, cell.getNumericCellValue());
                                    }
                                    break;
                                case BOOLEAN:
                                    statement.setBoolean(colIndex + 1, cell.getBooleanCellValue());
                                    break;
                                default:
                                    statement.setNull(colIndex + 1, Types.NULL);
                                    break;
                            }
                        }
                    } else {
                        statement.setNull(colIndex + 1, Types.NULL);
                    }
                }

                Cell longitudeCell = nextRow.getCell(14, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                Cell latitudeCell = nextRow.getCell(15, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                double longitude = 0.0;
                double latitude = 0.0;

                if (longitudeCell != null && longitudeCell.getCellType() == CellType.NUMERIC) {
                    longitude = longitudeCell.getNumericCellValue();
                }

                if (latitudeCell != null && latitudeCell.getCellType() == CellType.NUMERIC) {
                    latitude = latitudeCell.getNumericCellValue();
                }

                statement.setDouble(34, longitude);
                statement.setDouble(35, latitude);
                statement.addBatch();
            }

            statement.executeBatch();
            connection.commit();

            workbook.close();
            inputStream.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void uploadExcel2Data(){

        String jdbcURL = "jdbc:postgresql://localhost:5432/hysz_sponge?currentSchema=public";
        String username = "postgres";
        String password = "root123456";
        String excelFilePath = "E:\\data\\hy_spong\\line.xlsx";

        try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
            FileInputStream inputStream = new FileInputStream(excelFilePath);
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = firstSheet.iterator();
            connection.setAutoCommit(false);
            String sql = "INSERT INTO hy_pipline " +
                    "(id,pipeline_segment_number, start_point_number, end_point_number, start_depth, start_elevation, " +
                    "end_depth, end_elevation, material, protective_material, burial_method, " +
                    "section_size, construction_year, ownership_unit, line_type, cable_number, " +
                    "voltage_value, pressure_carrier, total_holes, used_holes, survey_year, " +
                    "update_year, road_name, pipeline_type, remark, shape_length) " +
                    "VALUES (?,?, ?, ?, ?, ?, " +
                    "?, ?, ?, ?, ?, " +
                    "?, ?, ?, ?, ?, " +
                    "?, ?, ?, ?, ?, " +
                    "?, ?, ?, ?, ?)";


            PreparedStatement statement = connection.prepareStatement(sql);


            while (rowIterator.hasNext()) {
                Row nextRow = rowIterator.next();
                if (nextRow.getRowNum() == 0) {
                    // 跳过标题行
                    continue;
                }
                int totalHolesIndex  = 25;
                for (int colIndex = 0; colIndex < 26; colIndex++) { // 保证处理 33 个列
                    Cell cell = nextRow.getCell(colIndex, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);

                    if (cell != null) {
                        if (colIndex == 0) { // 假设第一列是 id
                            double idValue = cell.getNumericCellValue();
                            statement.setInt(1, (int) Math.round(idValue));
                        } else {
                            switch (cell.getCellType()) {
                                case STRING:
                                    statement.setString(colIndex + 1, cell.getStringCellValue());
                                    break;
                                case NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        // 对于日期类型的数据
                                        statement.setDate(colIndex + 1, new java.sql.Date(cell.getDateCellValue().getTime()));
                                    }
                                  else {
                                        // 对于其他数字类型的数据
                                        statement.setDouble(colIndex + 1, cell.getNumericCellValue());
                                    }
                                    break;
                                case BOOLEAN:
                                    statement.setBoolean(colIndex + 1, cell.getBooleanCellValue());
                                    break;
                                default:
                                    statement.setNull(colIndex + 1, Types.NULL);
                                    break;
                            }
                        }
                    } else {
                        statement.setNull(colIndex + 1, Types.NULL);
                    }
                }


                statement.addBatch();
            }

            statement.executeBatch();
            connection.commit();

            workbook.close();
            inputStream.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
